﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cecbData;

namespace cecbClasses.Data_Adapters
{
    public class Func_Members
    {
        public Func_Members()
        {
            dataContext = new DSSEntities();
        }

       DSSEntities dataContext;
        Members mem;
        MemberContacts memContactNo;
        MemberEmails memEmail;





        // Sapu methods ===================================================


        // insert member method 
        public bool InsertMember(String name, String address, String designation, String specilised, String contactNo, String email)
        {
            bool IsInserted = false;
            try
            {
                String reference = getNewMemberReference();
                mem = new Members();
                mem.mem_reference = reference;
                mem.mem_name = name;
                mem.mem_designation = designation;
                mem.mem_address = address;
                //  mem.mem_specilised = specilised;

                dataContext.AddToMembers(mem);
                dataContext.SaveChanges();

                // insert data to the EmployeeContact table
                memContactNo = new MemberContacts();
                memContactNo.mem_reference = reference;
                memContactNo.mem_contactNo = contactNo;
                dataContext.AddToMemberContacts(memContactNo);
                dataContext.SaveChanges();

                // insert data to the Employee email table
                memEmail = new MemberEmails();
                memEmail.mem_reference = reference;
                memEmail.mem_email = email;
                dataContext.AddToMemberEmails(memEmail);
                dataContext.SaveChanges();

                IsInserted = true;
            }
            catch (Exception)
            {

                IsInserted = false;
            }


            return IsInserted;
        }
        // get all informations from member table
        public List<Members> getAllMembers()
        {
            IQueryable<Members> query = from c in dataContext.Members

                                        select c;

            List<Members> Memdetails = query.ToList();

            return Memdetails;
        }
        // get the member contact details trough mem_reference
        public List<MemberContacts> getMemberContatcs(String memId)
        {
            IQueryable<MemberContacts> query = from c in dataContext.MemberContacts

                                               where c.mem_reference == memId
                                               select c;

            List<MemberContacts> Memdetails = query.ToList();

            return Memdetails;
        }

        // get the member Email details through the mem_referance
        public List<MemberEmails> getMemberEmail(String memId)
        {
            IQueryable<MemberEmails> query = from c in dataContext.MemberEmails
                                             where c.mem_reference == memId
                                             select c;

            List<MemberEmails> Memdetails = query.ToList();

            return Memdetails;
        }
        // insert consultant basic informations
        public bool InsertConsultantBasicInfo(String name, String address, String designation, String specilised, List<String> cno, List<String> em)
        {
            bool IsInserted = false;
            try
            {
                String reference = getNewMemberReference();
                mem = new Members();
                mem.mem_reference = reference;
                mem.mem_name = name;
                mem.mem_designation = designation;
                mem.mem_address = address;
                // mem.mem_specilised = specilised;

                dataContext.AddToMembers(mem);
                dataContext.SaveChanges();

                foreach (String no in cno)
                {
                    memContactNo = new MemberContacts();
                    memContactNo.mem_reference = reference;
                    memContactNo.mem_contactNo = no;
                    dataContext.AddToMemberContacts(memContactNo);
                    dataContext.SaveChanges();
                }

                foreach (String mail in em)
                {
                    memEmail = new MemberEmails();
                    memEmail.mem_reference = reference;
                    memEmail.mem_email = mail;
                    dataContext.AddToMemberEmails(memEmail);
                    dataContext.SaveChanges();
                }



                IsInserted = true;
            }
            catch (Exception)
            {

                IsInserted = false;
            }


            return IsInserted;
        }



      
        public String getNewMemberReference()
        {

            var idKey = (from c in dataContext.Members
                         orderby c.mem_id descending
                         select c).First();


            String id = idKey.mem_reference.ToString();
            int sub;
            try
            {
                sub = int.Parse(id.Substring(3)) + 1;
            }
            catch (Exception)
            {
                sub = 1;
            }
            String newId = "MEM" + sub;
            return newId.ToString();

        }
        // get data of member specialized area as a list
        public List<Components> getSpecializatioInformations()
        {
            IQueryable<Components> Query =
                  from c in dataContext.Components

                  select c;

            List<Components> SelectedEmpList = Query.ToList();

            return SelectedEmpList;

        }
        // update the member Email details
        public bool updateMemberEmails(String eno, String memid, String oldEno)
        {
            bool IsUpdated = false;
            try
            {
                dataContext.ExecuteStoreCommand("update MemberEmails set mem_email ='" + eno + "' where mem_reference = '" + memid + "' and mem_email ='" + oldEno + "' ");
                IsUpdated = true;
            }
            catch (Exception)
            {

                IsUpdated = false;
            }

            return IsUpdated;

        }
        
        public bool updateMemberContact(String eno, String memid, String oldEno)
        {
            bool IsUpdated = false;
            try
            {
                dataContext.ExecuteStoreCommand("update MemberContacts set mem_contactNo ='" + eno + "' where mem_reference = '" + memid + "' and mem_contactNo ='" + oldEno + "' ");
                IsUpdated = true;
            }
            catch (Exception)
            {

                IsUpdated = false;
            }

            return IsUpdated;

        }
        //get the member name through member referance
        public string getMemberName(String memId)
        {
            string name = "";
            try
            {

                var quary = (from c in dataContext.Members
                             where c.mem_reference == memId
                             select c.mem_name).First();
                name = quary;


            }
            catch (Exception)
            {

            }
            return name;
        }
        // get member details as a list through member referance
        public List<Members> getMemberDetails(string mem_id)
        {
            IQueryable<Members> query = from c in dataContext.Members
                                        where c.mem_reference == mem_id

                                        select c;

            List<Members> Memdetails = query.ToList();

            return Memdetails;
        }

        // To get the member login details
        public List<MemberLogin> getMemberLoginDetails(string mem_id)
        {
            IQueryable<MemberLogin> query = from c in dataContext.MemberLogin
                                            where c.mem_reference == mem_id

                                            select c;

            List<MemberLogin> Memdetails = query.ToList();

            return Memdetails;
        }

        // update the member details

        public void updateMemberDetails(string mem_ref, string mem_address, string mem_name, string mem_designation)
        {
            try
            {


                Members memberObject = dataContext.Members.First(i => i.mem_reference == mem_ref);

                memberObject.mem_address = mem_address;
                memberObject.mem_name = mem_name;
                memberObject.mem_designation = mem_designation;
                dataContext.SaveChanges();

            }
            catch (Exception)
            {
                throw;
            }

        }
        // update the member contact details
        public void uodateMemberContactDetails(string memId, string Eaddress)
        {
            try
            {

                MemberEmails memEmail = dataContext.MemberEmails.First(i => i.mem_reference == memId);
                memEmail.mem_email = Eaddress;
                dataContext.SaveChanges();
            }
            catch (Exception)
            {


            }
        }

        public List<Members> getConsultents()
        {
            IQueryable<Members> query = from c in dataContext.Members
                                        where c.mem_designation == "Consultent"

                                        select c;

            List<Members> Memdetails = query.ToList();

            return Memdetails;
        }


    }
}
